package com.situ.mall.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.mall.dao.IUserDao;
import com.situ.mall.pojo.User;
import com.situ.mall.utils.JDBCUtil;
import com.situ.mall.vo.DeptCountVO;
import com.situ.mall.vo.UserDeptVO;

public class UserDaoImpl implements IUserDao{

	@Override
	public List<UserDeptVO> selectAll() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<UserDeptVO> list = new ArrayList<UserDeptVO>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select u.id,dept_id,username,password,email,role,d.`name` deptName\r\n" + 
					"from user as u inner join dept as d\r\n" + 
					"on u.dept_id=d.id";
			statement = connection.prepareStatement(sql);
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				//每遍历一次封装一个User对象，然后放到List集合里面
				int id = resultSet.getInt("id");
				int deptId = resultSet.getInt("dept_id");
				String username = resultSet.getString("username");
				String password = resultSet.getString("password");
				String email = resultSet.getString("email");
				int role = resultSet.getInt("role");
				String deptName = resultSet.getString("deptName");
				UserDeptVO userDeptVO = new UserDeptVO(id, deptId, username, password, email, role, deptName);
				list.add(userDeptVO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public void deleteById(int id) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "delete from user where id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			System.out.println(statement);
			statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public void add(User user) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "insert into user(dept_id,username,password,email,role) values(?,?,?,?,?)";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, user.getDeptId());
			statement.setString(2, user.getUsername());
			statement.setString(3, user.getPassword());
			statement.setString(4, user.getEmail());
			statement.setInt(5, user.getRole());
			System.out.println(statement);
			statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}

	@Override
	public List<DeptCountVO> selectDeptCount() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<DeptCountVO> list = new ArrayList<DeptCountVO>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT d.name, count(*) as value FROM user as u INNER JOIN dept as d ON u.dept_id=d.id  GROUP BY u.dept_id";
			statement = connection.prepareStatement(sql);
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				String name = resultSet.getString("name");
				int count = resultSet.getInt("value");
				DeptCountVO deptCountVO = new DeptCountVO(name, count);
				list.add(deptCountVO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public User login(String username, String password) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		User user = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id,username,password,email,dept_id,role from user where username=? and password=?";
			statement = connection.prepareStatement(sql);
			statement.setString(1, username);
			statement.setString(2, password);
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				int deptId = resultSet.getInt("dept_id");
				String name = resultSet.getString("username");
				String pwd = resultSet.getString("password");
				String email = resultSet.getString("email");
				int role = resultSet.getInt("role");
				user = new User(id, deptId, name, pwd, email, role);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return user;
	}

	@Override
	public User selectById(Integer id) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		User user = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id,username,password,email,dept_id,role from user where id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int deptId = resultSet.getInt("dept_id");
				String name = resultSet.getString("username");
				String pwd = resultSet.getString("password");
				String email = resultSet.getString("email");
				int role = resultSet.getInt("role");
				user = new User(id, deptId, name, pwd, email, role);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return user;
	}

	@Override
	public void update(User user) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "update user set username=?,dept_id=?,password=?,email=?,role=? where id=?";
			statement = connection.prepareStatement(sql);
			statement.setString(1, user.getUsername());
			statement.setInt(2, user.getDeptId());
			statement.setString(3, user.getPassword());
			statement.setString(4, user.getEmail());
			statement.setInt(5, user.getRole());
			statement.setInt(6, user.getId());
			System.out.println(statement);
			statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}

}
